#!/usr/bin/env python
#coding=utf-8

import urllib,urllib2,sys,cookielib,time
import mysql.connector,json
import ast #str to int
from lxml import etree

headers = {
	'Accept':'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
	'Cache-Control':'max-age=0',
	'User-Agent':'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.139 Safari/537.36',
	'Accept-Language':'zh-CN,zh;q=0.8',
	'Connection':'keep-alive'
}
#初始化urllib2的数据
def initurllib2():
    cookie_support= urllib2.HTTPCookieProcessor(cookielib.CookieJar())
    opener = urllib2.build_opener(cookie_support, urllib2.HTTPHandler)
    urllib2.install_opener(opener)
    cookiejar = cookielib.CookieJar()
    urlOpener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cookiejar))
    return  urlOpener,cookiejar
#请求传入的URL网页
def gethtml(url,urlOpener):
    try:
        conntent=urlOpener.open(url, timeout=10).read()
        return conntent
    except Exception, e:
        print e
        return False

def connectdb():
	try:
		con=mysql.connector.connect(host='localhost',port=3306,user='root',password='password',database='stockdb',charset='utf8',buffered=True)
		print "connect mysql sucess!!!"
	except mysql.connector.Error as e:
		print 'connect fails!{}' % format(e)
	return con
def existstock(db,stockcode,stocktype):
	cursor=db.cursor()
	sqlstr="select count(*) from t_stock_info where stockcode=%s and stocktype=%s"
	paramdata=[]
	paramdata.append(stockcode)
	paramdata.append(stocktype)
	try:
		cursor.execute(sqlstr,paramdata)
	except mysql.connect.Error as e:
		print 'connect fails!{}' % format(e)
	ret=cursor.fetchall()
	if ret[0][0] == 0:
		return True
	else:
		return False
def existstockforhistory(db,stockcode,stocktype,trandate):
	cursor=db.cursor()
	sqlstr="select count(*) from t_stock_history where stockcode=%s and stocktype=%s and substr(trandate,1,4)=%s"
	paramdata=[]
	paramdata.append(stockcode)
	paramdata.append(stocktype)
	paramdata.append(trandate)
	cursor.execute(sqlstr,paramdata)
	#print "%s %s %s" % (stockcode,stocktype,trandate)
	ret=cursor.fetchall()
	if ret[0][0] != 0:
		return False
	else:
		return True	
def maxupdate(db,stockcode,stocktype):
	cursor=db.cursor()
	sqlstr="select date_format(date_sub(max(trandate),interval -1 day),'%Y%m%d') from t_stock_history where stockcode=%s and stocktype=%s"
	paramdata=[]
	paramdata.append(stockcode)
	paramdata.append(stocktype)
	cursor.execute(sqlstr,paramdata)
	#print "%s %s %s" % (stockcode,stocktype)
	ret=cursor.fetchall()
	return ret[0][0]
def insertstock(db,stockcode,stocktype):
	cursor=db.cursor()
	sqlstr="insert into t_stock_info(stockcode,stocktype) values(%s,%s)"
	print "sqlstr:" + sqlstr
	paramdata=[]
	paramdata.append(stockcode)
	paramdata.append(stocktype)
	print paramdata
#	try:
	cursor.execute(sqlstr,paramdata)
#	except mysql.connect.Error as e:
#		print 'connect fails!{}' % format(e)	
	db.commit()
	print 'insert sql ' + stockcode + " " + stocktype
def updatestock(db,stockcode,stocktype):
	cursor=db.cursor()
	getstockinfourl='http://qt.gtimg.cn/q='
	geturl=getstockinfourl+stocktype + stockcode
	print 'geturl==>' + geturl
	req = urllib2.Request (
		url = geturl,
		headers = headers
	)
	page=gethtml(req,urlOpener) 
	#page='''v_sz000001="51~平安银行~000001~10.68~10.75~10.73~710509~359400~351110~10.67~6193~10.66~13018~10.65~9908~10.64~1640~10.63~2561~10.68~2999~10.69~1281~10.70~2772~10.71~2156~10.72~1938~15:00:04/10.68/8425/S/8997900/9600|14:57:00/10.68/391/S/417588/9516|14:56:57/10.68/616/S/657888/9515|14:56:54/10.68/186/S/198661/9513|14:56:51/10.68/527/B/562838/9511|14:56:48/10.68/255/B/272340/9509~20180504150106~-0.07~-0.65~10.83~10.66~10.68/702084/752304569~710509~76130~0.42~7.78~~10.83~10.66~1.58~1806.84~1833.80~0.90~11.83~9.68~0.52~22174~10.72~6.95~7.91";'''
	if page is False:
		print "get url timeout!, next stock"
		return 
	#page = unicode(page, "GBK")
	#print page
	p= page.split('~')
	if len(p) <= 53:
		return
	stockname=p[1].decode('gbk')
	s= p[30].encode("gbk")
	lastupdate = s[:8]
	lastuptime = s[8:]
	upQuota=p[31]
	upQuotarate=p[32]
	higamt=p[33]
	miniamt=p[34]
	curramt=p[35].split('/')[0]
	trannum=p[36]
	tranamt=p[37]
	trate=p[38]
	pe=p[39]
	zf=p[43]    
	flowtotal=p[44] 
   	total=p[45]	
	pb=p[46]
	dataupdate=time.strftime('%Y%m%d',time.localtime())
#	print stockname.decode("GBK"),lastupdate,lastuptime,p[2],p[3],p[4],p[5],curramt,p[7],p[8],p[9],p[10],p[11],p[12],p[13],p[14]
	print stockname,lastupdate,lastuptime,upQuota,upQuotarate,higamt,miniamt,curramt,trannum,tranamt,trate,pe,zf,flowtotal,total,pb
	sqlstr="update t_stock_info set stockname=%s,lastupdate=%s,lastuptime=%s,upQuota=%s,upQuotarate=%s,higamt=%s"
	sqlstr+=",miniamt=%s,curramt=%s,trannum=%s,tranamt=%s,trate=%s,pe=%s,zf=%s,total=%s,flowtotal=%s,pb=%s,dataupdate=%s "
	sqlstr+=" where stockcode=%s and stocktype=%s"
	
	#print "sqlstr:" + sqlstr
	paramdata=[]
#	paramdata.append(stockname.decode('gbk'))
	paramdata.append(stockname)
	paramdata.append(lastupdate)
	paramdata.append(lastuptime)
	paramdata.append(ast.literal_eval(upQuota))
	paramdata.append(upQuotarate)
	paramdata.append(ast.literal_eval(higamt))
	paramdata.append(ast.literal_eval(miniamt))
	paramdata.append(ast.literal_eval(curramt))
	paramdata.append(ast.literal_eval(trannum))
	paramdata.append(ast.literal_eval(tranamt))	
	paramdata.append(trate)	
	paramdata.append(pe)	
	paramdata.append(zf)	
	paramdata.append(ast.literal_eval(total))
	paramdata.append(ast.literal_eval(flowtotal))
	paramdata.append(pb)
	paramdata.append(dataupdate)
	paramdata.append(stockcode)
	paramdata.append(stocktype)

	#print paramdata
	cursor.execute(sqlstr,paramdata)
	db.commit()	
def insertstockforhistory(db,stockcode,stocktype,updatectrl):
	cursor=db.cursor()
	#当前日期
	nowdate=time.strftime('%Y',time.localtime())
	dataupdate=time.strftime('%Y%m%d',time.localtime())	
	inowdate=int(nowdate)
	inextdate=inowdate+1

	while inextdate >= 1990:
		startdate=str(inowdate)
		enddate=str(inextdate)
		#可重复获取，添加重复判断
		historyurl="http://web.ifzq.gtimg.cn/appstock/app/fqkline/get?_var=kline_dayqfq&param="				
		if updatectrl is None:
			#获取全部历史数据
			if existstockforhistory(db,stockcode,stocktype,startdate) is False:
				print '本年度:' + startdate  + '数据已经存在,跳过本年'
				inextdate-=1
				inowdate-=1
				continue
			historyurl+=stocktype + stockcode + ",day," + str(startdate) + "-1-1," + enddate + "-1-1,400,qfqzxt"			
		else:
			#获取今年历史数据
			s=time.strptime(updatectrl,"%Y%m%d")
			historyurl+=stocktype + stockcode + ",day," + str(s.tm_year) + '-' + str(s.tm_mon) + '-' + str(s.tm_mday) + "," + enddate + "-1-1,400,qfqzxt"
		print historyurl
		page = gethtml(historyurl,urlOpener)
		if page is False:
			print "get url timeout!, next stock"
			inextdate-=1
			inowdate-=1
			continue
		arr = json.loads(page.split("=")[1])
		detail=0
		for day in  arr['data'][stocktype+stockcode]['day']:
			detail=+1
			trandate=day[0].replace('-','')
			statamt=day[1]
			endamt=day[2]
			higamt=day[3]
			miniamt=day[4]
			totalamt=day[5]
			upQuota=float((endamt)) - float((statamt))
			upQuotarate=float(upQuota) / float((statamt))
			print "%s%s,%s,%s,%s,%s,%s,%s," % (stocktype,stockcode,trandate,statamt,endamt,higamt,miniamt,totalamt),
			print "%f,%f," % (upQuota,upQuotarate),
			print dataupdate
			sqlstr="insert into t_stock_history (stockcode,stocktype,trandate,statamt,endamt,higamt,miniamt,totalamt,upQuota,upQuotarate,dataupdate) "
			sqlstr+=" values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
			paramdata=[]
			paramdata.append(stockcode)
			paramdata.append(stocktype)
			paramdata.append(trandate)
			paramdata.append(statamt)
			paramdata.append(endamt)
			paramdata.append(higamt)
			paramdata.append(miniamt)
			paramdata.append(totalamt)
			paramdata.append(upQuota)
			paramdata.append(upQuotarate)
			paramdata.append(dataupdate)
			cursor.execute(sqlstr,paramdata)
		if detail == 0:
			break
		db.commit()	
		inextdate-=1
		inowdate-=1
		#程序自动跑,只获取一年的数据
		break

if __name__=="__main__":  
	db=connectdb()
	urlOpener,cookiejar=initurllib2()
	getstockallurl="http://quote.stockstar.com/stock/stock_index.htm"
	page=gethtml(getstockallurl,urlOpener)
	page = unicode(page, "ISO-8859-1")
	selector = etree.HTML(page) 
	result = etree.tostring(selector)
	#上海A股 <ul class="seo_pageList" id="index_data_0">
	stockcode = selector.xpath(u'//ul[@id="index_data_0"]/li/span/a')
	#中文乱码问题，不获取中文名称
	#stockname = selector.xpath(u'//ul[@id="index_data_0"]/li/a')
	newstock=0
	print('获取上海A股共有%d') % (len(stockcode))
	for i in stockcode:
		##根据股票代码和类型查询是否存在
		#print(i.text)
		if existstock(db,i.text,'sh') :
			insertstock(db,i.text,'sh')
			newstock+=1
	print '本次新增加上海A股共%d个' % (newstock)
	#深圳A股 <ul class="seo_pageList" id="index_data_2">
	stockcode = selector.xpath(u'//ul[@id="index_data_2"]/li/span/a')
	#中文乱码问题，不获取中文名称
	#stockname = selector.xpath(u'//ul[@id="index_data_2"]/li/a')
	print('获取深圳A股共有%d') % (len(stockcode))
	j=0
	for i in stockcode:
		##根据股票代码和类型查询是否存在
		#print(i.text)
		if existstock(db,i.text,'sz') :
			insertstock(db,i.text,'sz')
			newstock+=1
			j+=1
	print '本次新增加深圳A股共%d个' % (j)
	sqlstr="select dataupdate from t_stock_ctrl"
	c=db.cursor()
	c.execute(sqlstr)
	#date=c.fetchall()
	#dataupdate="".join(list(date))
	for dataupdate in c:
		None
	dataupdate="".join(list(dataupdate))
	#获取当前股票信息,腾讯股票
	sqlstr="select stockcode,stocktype from t_stock_info where dataupdate<%s or dataupdate is NULL"
	c=db.cursor()
	paramdata=[]
	paramdata.append(dataupdate)
	c.execute(sqlstr,paramdata)
	
	#获取当天最近一天的数据
	for stockcode,stocktype in c:
		updatestock(db,stockcode,stocktype)
		break
	#获取股票历史数据
	sqlstr="select stockcode,stocktype from t_stock_info"
	c=db.cursor()
	c.execute(sqlstr)
	for stockcode,stocktype in c:
		dataupdate=maxupdate(db,stockcode,stocktype)
		insertstockforhistory(db,stockcode,stocktype,dataupdate)

	#更新最后的更新日期
	sqlstr="update t_stock_ctrl set dataupdate=%s"
	lastupdate=time.strftime('%Y%m%d',time.localtime())	
	c=db.cursor()
	paramdata=[]
	paramdata.append(lastupdate)
	c.execute(sqlstr,paramdata)
	db.commit()
	print 'lastupdate:' + lastupdate
	print 'task over'
